import re
from pymysql import connect


# 定义一个空子典
URL_FUNC_DICT = dict()


def route(url):
    """
    带参数的装饰器来做路由
    :param url: 请求的url
    :return: 返回一个装饰器
    """
    def set_func(func):
        # 往空子典里面添加元素
        URL_FUNC_DICT[url] = func

        def call_func(*args, **kwargs):
           return func(*args, **kwargs)

        return call_func

    return set_func


@route(r"/index.html")
def index(ret):

    with open("./templates/index.html") as f:
        content = f.read()

    # my_stock_info = "测试一下，数据"

    # 创建Connection连接
    conn = connect(host='ironman.ren', port=3306, database='stock_db', user='root', password='456123love', charset='utf8')
    # 获得Cursor对象
    cursor = conn.cursor()
    cursor.execute("select * from info;")
    stock_infos = cursor.fetchall()
    cursor.close()
    conn.close()

    tr_template = """<tr>
        <td>%s</td>
        <td>%s</td>
        <td>%s</td>
        <td>%s</td>
        <td>%s</td>
        <td>%s</td>
        <td>%s</td>
        <td>%s</td>
        <td>
          <input type="button" value="添加" id="toAdd" name="toAdd" systemidvalue="000007">
        </td>
        </tr>
        """

    html = ""

    for line_info in stock_infos:

        html += tr_template%(line_info[0],line_info[1],line_info[2],line_info[3],line_info[4],line_info[5],line_info[6],line_info[7])

    content = re.sub(r"\{%content%\}", str(html), content)

    return content


@route(r"/center.html")
def center(ret):
    with open("./templates/center.html") as f:
        content = f.read()

        # 创建Connection连接
        conn = connect(host='ironman.ren', port=3306, database='stock_db', user='root', password='456123love',
                       charset='utf8')
        # 获得Cursor对象
        cursor = conn.cursor()
        cursor.execute("select i.code,i.short,i.chg,i.turnover,i.price,i.highs,f.note_info from info as i inner join focus as f on i.id = f.info_id;")
        stock_infos = cursor.fetchall()
        cursor.close()
        conn.close()

        tr_template = """<tr>
            <td>%s</td>
            <td>%s</td>
            <td>%s</td>
            <td>%s</td>
            <td>%s</td>
            <td>%s</td>
            <td>%s</td>
            <td>
              <a type="button" class="btn btn-default btn-xs" href="/update/%s.html"> <span class="glyphicon glyphicon-star" aria-hidden="true"></span> 修改 </a>
            </td>
            <td>
              <input type="button" value="删除" id="toDel" name="toDel" systemidvalue="%s">
            </td>
            </tr>
            """

        html = ""

        for line_info in stock_infos:
            html += tr_template % (
            line_info[0], line_info[1], line_info[2], line_info[3], line_info[4], line_info[5], line_info[6],line_info[0],line_info[0])

        content = re.sub(r"\{%content%\}", str(html), content)

        return content


@route(r"/update/(\d+).html")
def update(ret):
    with open("./templates/update.html") as f:
        content = f.read()
    return content

# 给路由添加正则表达式的原因：在实际开发时，url中往往会带有很多的参数，例如： /add/0003.html 中0003 就是参数，
# 如果没有正则的话，那么就需要编写 N 次 @route 来进行添加 url 对应的函数到字典中，此时字典中的键值对有 N 个，浪费空间
# 而采用了正则的话，那么只要编写1次 @route 就可以完成多个 url 例如/add/0008.html /add/0003.html 等对同一个函数，此时的字典中键值对
# 减少很多
@route(r"/add/(\d+)\.html")
def add_focus(ret):

    # 1.获取股票代码
    stock_code = ret.group(1)

    # 2.判断下是否有这个股票代码
    # 创建Connection连接
    conn = connect(host='ironman.ren', port=3306, database='stock_db', user='root', password='456123love',
                   charset='utf8')
    # 获得Cursor对象
    cursor = conn.cursor()
    sql = """select * from info where code=%s;"""
    cursor.execute(sql,(stock_code,))

    print("************-----------1------------------")

    # 如果要是没有这个股票代码，那么就认为是非法的请求
    if not cursor.fetchone():
        cursor.close()
        conn.close()
        return "没有这支股票，大哥 ，我们是创业公司，请手下留情..."

    print("************-----------2------------------")

    # 3.在股票存在的情况下查看股票是否关注过
    sql = """select * from focus as f inner join info as i on f.info_id = i.id where i.code=%s;"""
    cursor.execute(sql,(stock_code,))
    # 如果查出来表示关注过
    if cursor.fetchone():
        cursor.close()
        conn.close()
        return "已经关注过了，请勿重复关注..."

    print("************-----------3------------------")

    # 4.在没有关注的情况下，进行关注，也就是往关注的信息表里面添加数据
    sql = """insert into focus (info_id) select id from info where code = %s;"""
    cursor.execute(sql,(stock_code,))
    conn.commit()
    cursor.close()
    conn.close()

    return "关注成功"


@route(r"/del/(\d+)\.html")
def del_focus(ret):

    # 1.获取股票代码
    stock_code = ret.group(1)

    # 2.判断下是否有这个股票代码
    # 创建Connection连接
    conn = connect(host='ironman.ren', port=3306, database='stock_db', user='root', password='456123love',
                   charset='utf8')
    # 获得Cursor对象
    cursor = conn.cursor()
    sql = """select * from info where code=%s;"""
    cursor.execute(sql,(stock_code,))

    print("************-----------1------------------")

    # 如果要是没有这个股票代码，那么就认为是非法的请求
    if not cursor.fetchone():
        cursor.close()
        conn.close()
        return "没有这支股票，大哥 ，我们是创业公司，请手下留情..."

    print("************-----------2------------------")

    # 3.在股票存在的情况下查看股票是否关注过
    sql = """select * from focus as f inner join info as i on f.info_id = i.id where i.code=%s;"""
    cursor.execute(sql,(stock_code,))
    # 如果查出来表示关注过
    if not cursor.fetchone():
        cursor.close()
        conn.close()
        return "%s 之前没有关注过，请勿取消关注..."%stock_code

    print("************-----------3------------------")

    # 4.在有关注的情况下，进行取消关注，也就是往关注的信息表里面删除数据
    sql = """delete from focus where info_id = (select id from info where code=%s);"""
    cursor.execute(sql,(stock_code,))
    conn.commit()
    cursor.close()
    conn.close()

    return "取消关注成功"


def application(evn, start_reponse):
    start_reponse('200 OK', [('Content-Type', 'text/html;charset=utf-8')])

    file_name = evn['PATH_INFO']

    try:

        for url,func in URL_FUNC_DICT.items():
            ret = re.match(url,file_name)
            if ret:
                return func(ret)
        else:
            return "请求的url(%s)没有对应的函数...." % file_name

    except Exception as ret:
        return "产生了异常：%s"% str(ret)